package com.banfeiguanli.springboot.mapper;

import com.banfeiguanli.springboot.entity.ClassNotice;
import com.banfeiguanli.springboot.entity.dto.ClassNoticeDTO;
import org.apache.ibatis.annotations.*;
import org.springframework.context.annotation.ComponentScan;

import java.util.List;

@Mapper
@ComponentScan
public interface ClassNoticeMapper {
    @Insert("insert into classNotice (NoticeTitle, NoticeContent, CN_ClassAdminID)" +
            "values (#{noticeTitle}, #{noticeContent}," +
            "(select ClassAdminID from classadmin where CA_ClassID = " +
            "(select UI_ClassID from userinfo where UserID = #{userID} )))")
    Integer createClassNotice(@Param("noticeTitle") String noticeTitle, @Param("noticeContent") String noticeContent, @Param("userID") int userID);
    //删除班级通知
    @Delete("delete from classNotice where ClaNoticeID = #{classNoticeID}")
    Integer deleteClassNotice(@Param("classNoticeID") int classNoticeID);
    @Update("update classnotice set NoticeTitle = #{classNoticeTitle}, NoticeContent = #{classNoticeContent} where ClaNoticeID = #{classNoticeID}")
    Integer changeClassNotice(@Param("classNoticeID") int classNoticeID,@Param("classNoticeTitle") String classNoticeTitle,@Param("classNoticeContent") String classNoticeContent);
    //满足条件班级通知
    @Select("select ClaNoticeID, releaseTime, noticeTitle, NoticeContent " +
            "from classnotice " +
            "where CN_ClassAdminID in " +
            "(select ClassAdminID from classadmin where CA_ClassID = " +
            "(select UI_ClassID from userinfo where UserID = #{userID})) " +
            "and NoticeTitle like concat('%', #{searchNoticeTitle}, '%')"+
            "order by ReleaseTime " +
            "desc limit #{classNoticePageNumber}, #{classNoticePageSize}")
    List<ClassNotice> getAllClassNotice(@Param("userID") int userID, @Param("searchNoticeTitle") String searchNoticeTitle, @Param("classNoticePageNumber") int classNoticePageNumber, @Param("classNoticePageSize") int classNoticePageSize);
    //获得满足条件班级通知条数
    @Select("select count(*) " +
            "from classnotice " +
            "where CN_ClassAdminID = " +
            "(select ClassAdminID from classadmin where CA_ClassID = " +
            "(select UI_ClassID from userinfo where UserID = #{userID}))" +
            "and NoticeTitle like concat('%', #{searchNoticeTitle}, '%')")
    Integer getAllClassNoticeNum(@Param("userID") int userID, @Param("searchNoticeTitle") String searchNoticeTitle);
    //班级全部通知
    @Select("select ClaNoticeID, releaseTime, noticeTitle, NoticeContent " +
            "from classnotice where CN_ClassAdminID in " +
            "(select ClassAdminID from classadmin where CA_ClassID = " +
            "(select UI_ClassID from userinfo where UserID = #{userID})) ")
    List<ClassNotice> getClassNotice(@Param("userID") int userID);

    //获取班级通知(后台管理)
    @Select("select claNoticeID, noticeTitle, noticeContent, releaseTime, " +
            "classAdminID, classID, className, userID, userName " +
            "from classnotice,bjgly " +
            "where NoticeTitle like concat('%', #{searchTitle}, '%') " +
            "and CN_ClassAdminID = ClassAdminID " +
            "order by ReleaseTime desc " +
            "limit #{pageNumber}, #{pageSize} ")
    List<ClassNotice> getClassNoticeForBack(@Param("pageNumber") Integer pageNumber,
                                            @Param("pageSize") Integer pageSize,
                                            @Param("searchTitle") String searchTitle);

    //统计总数
    @Select("select count(*)" +
            "from classnotice " +
            "where NoticeTitle like concat('%', #{searchTitle}, '%')")
    Integer countAllClassNoticeTotal(@Param("searchTitle") String searchTitle);

    //删除班级通知(后台管理)
    @Delete("delete from classnotice where ClaNoticeID = #{calNoticeID}")
    Integer deleteClassNoticeForBack(@Param("calNoticeID") Integer calNoticeID);
}
